package middol.utils

import grails.gorm.transactions.Transactional
import org.apache.poi.ss.formula.functions.T
import org.springframework.beans.factory.annotation.Autowired
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate

import javax.sql.DataSource

/**
* @Description:    Sql操作
* @Author:         zhangjiayu@middol.com
* @CreateDate:     2020/10/12 16:15
* @Version:        1.0
*/
@Transactional
class DataBaseService {

    @Autowired
    DataSource dataSource

    SaveOperationLog saveOperationLogService = new SaveOperationLog()

    /**
     * 分页查询
     * @param sql
     * @param mapSqlParameterSource
     * @return
     */
    def query(def sql, MapSqlParameterSource mapSqlParameterSource) {
        NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(dataSource)
        if (!mapSqlParameterSource) {
            mapSqlParameterSource = new MapSqlParameterSource()
        }
        String sqlList = DBUtil.getOraclePageSQL(sql, mapSqlParameterSource.getValues())
        String countSQL = DBUtil.getOracleCountSQL(sql)
        def results = jdbcTemplate.queryForList(sqlList, mapSqlParameterSource)
        results?.eachWithIndex { Map<String, Object> entry, int i ->
            entry?.eachWithIndex { Map.Entry<String, Object> map, int index ->
                def value = map?.value
                if (value == null) {
                    if (value instanceof Integer) {
                        map?.value = 0
                    }
                    if (value instanceof Double) {
                        map?.value = 0d
                    }
                    if (value instanceof BigDecimal) {
                        map?.value = 0.toBigDecimal()
                    }
                    if (value instanceof Date) {
                        map?.value = ""
                    }
                    if (value instanceof String) {
                        map?.value = ""
                    }
                }
            }
        }
        def totalCount = jdbcTemplate.queryForObject(countSQL, mapSqlParameterSource, Integer.class)
        return [
                "count": totalCount,
                "data" : results
        ]
    }
    /**
     * 查询数据列表
     * @param sql
     * @param mapSqlParameterSource
     * @return
     */
    def queryForList(def sql, MapSqlParameterSource mapSqlParameterSource = null) {
        NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(dataSource)
        if (!mapSqlParameterSource) {
            mapSqlParameterSource = new MapSqlParameterSource()
        }
        String sqlList = DBUtil.getOraclePageSQL(sql, mapSqlParameterSource.getValues())
        def results = jdbcTemplate.queryForList(sqlList, mapSqlParameterSource)
        results?.eachWithIndex { Map<String, Object> entry, int i ->
            entry?.eachWithIndex { Map.Entry<String, Object> map, int index ->
                def value = map?.value
                if (value == null) {
                    if (value instanceof Integer) {
                        map?.value = 0
                    }
                    if (value instanceof Double) {
                        map?.value = 0d
                    }
                    if (value instanceof BigDecimal) {
                        map?.value = 0.toBigDecimal()
                    }
                    if (value instanceof Date) {
                        map?.value = ""
                    }
                    if (value instanceof String) {
                        map?.value = ""
                    }
                }
            }
        }

        return results
    }

    /**
     * 查询数据对象
     * @param sql
     * @param mapSqlParameterSource
     * @return
     */
    def queryForMap(def sql, MapSqlParameterSource mapSqlParameterSource) {
        NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(dataSource)
        if (!mapSqlParameterSource) {
            mapSqlParameterSource = new MapSqlParameterSource()
        }
        String sqlList = DBUtil.getOraclePageSQL(sql, mapSqlParameterSource.getValues())
        def results = jdbcTemplate.queryForList(sqlList, mapSqlParameterSource)
        results?.eachWithIndex { Map<String, Object> entry, int i ->
            entry?.eachWithIndex { Map.Entry<String, Object> map, int index ->
                def value = map?.value
                if (value == null) {
                    if (value instanceof Integer) {
                        map?.value = 0
                    }
                    if (value instanceof Double) {
                        map?.value = 0d
                    }
                    if (value instanceof BigDecimal) {
                        map?.value = 0.toBigDecimal()
                    }
                    if (value instanceof Date) {
                        map?.value = ""
                    }
                    if (value instanceof String) {
                        map?.value = ""
                    }
                }
            }
        }

        def map = [:]
        if (results.size() > 0) {
            map = results.get(0)
        }

        return map
    }
    /**
     * 查询数据对象
     * @param sql
     * @param mapSqlParameterSource
     * @param requiredType
     * @return
     */
    T queryForObject(def sql, MapSqlParameterSource mapSqlParameterSource, Class<T> requiredType) {
        NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(dataSource)
        def results = jdbcTemplate.queryForObject(sql, mapSqlParameterSource, requiredType)
        return results
    }

    /**
     * 查询字符串数据（第一条数据）
     * @param sql
     * @param mapSqlParameterSource
     * @return
     */
    String queryForString(def sql, MapSqlParameterSource mapSqlParameterSource) {
        NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(dataSource)
        def results = jdbcTemplate.queryForList(sql, mapSqlParameterSource)
        def data = ""
        if (results && results.size() > 0) {
            data = results?.first()?.entrySet()?.first()?.value
        }
        return data?.toString()
    }

    /**
     * 执行修改、新增、删除等操作
     * @param sql
     * @param mapSqlParameterSource
     * @return
     */
    def update(String sql, MapSqlParameterSource mapSqlParameterSource) {
        NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(dataSource)
        int n = jdbcTemplate.update(sql, mapSqlParameterSource)
        saveOperationLogService.saveSQL(sql, mapSqlParameterSource)
        return n
    }

    /**
     * 执行sql列表
     * @param list
     * @return
     */
    @Transactional
    int[] exeManySQLWithTransactional(List<Map<String, MapSqlParameterSource>> list) {
        NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource)
        int[] bool = null;
        if (list != null && list.size() > 0) {
            bool = new int[list.size()];
            int i = 0;
            for (Map<String, MapSqlParameterSource> map : list) {
                for (Map.Entry<String, MapSqlParameterSource> entry : map.entrySet()) {
                    String sql = entry.getKey();
                    MapSqlParameterSource source = entry.getValue();
                    bool[i] = namedParameterJdbcTemplate.update(sql, source);
                    saveOperationLogService.saveSQL(sql, source)
                }
                i++;
            }
        }
        return bool;
    }
}
